package com.lntea.jdbc.spring;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlProvider;
import org.springframework.jdbc.core.StatementCallback;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringRunner;

import com.lntea.jdbc.spring.bean.GoodsStock;

@RunWith(SpringRunner.class)
@ContextConfiguration(locations={"classpath:com/lntea/jdbc/spring/jdbc-template.xml"})
public class JdbcTemplateTest {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Test
	public void testQueryForObject(){
		// query number of rows
		Integer count = jdbcTemplate.queryForObject("select count(*) from t_wms_goods_stock", Integer.class);
		System.out.println("count:" + count);
		// query using a bind variable
		Integer countOfWarehouseId = jdbcTemplate.queryForObject("select count(*) from t_wms_goods_stock where warehouse_id = ?", Integer.class, 1L);
		System.out.println("countOfWarehouseId:" + countOfWarehouseId);
		// query for a BigDecimal
		BigDecimal amount = jdbcTemplate.queryForObject("select amount from t_wms_goods_stock where id = ?", new Object[]{61}, BigDecimal.class);
		System.out.println("amount:" + amount);
		// query and populate a single domain object
		GoodsStock gs = jdbcTemplate.queryForObject("select * from t_wms_goods_stock where id = ?",
				new Object[]{61},
				new RowMapper<GoodsStock>() {

					public GoodsStock mapRow(ResultSet rs, int rowNum) throws SQLException {
						GoodsStock gs = new GoodsStock();
						gs.setId(rs.getInt("id"));
						gs.setWarehouseId(rs.getLong("warehouse_id"));
						return gs;
					}
				});
		System.out.println(gs);
		// query and populate a number of domain objects
		List<GoodsStock> gsList = jdbcTemplate.query("select * from t_wms_goods_stock limit 10",
				new RowMapper<GoodsStock>() {

					public GoodsStock mapRow(ResultSet rs, int rowNum) throws SQLException {
						GoodsStock gs = new GoodsStock();
						gs.setId(rs.getInt("id"));
						gs.setWarehouseId(rs.getLong("warehouse_id"));
						return gs;
					}
				});
		System.out.println(gsList);
	}
	
	@Test
	public void testUpdate(){
		int rowUpdateNum = jdbcTemplate.update("update t_wms_goods_stock set warehouse_id = ? where id = ?", 1L, 1);
		System.out.println("rowUpdateNum:" + rowUpdateNum);
	}
	
	// retrieve auto-generated keys
	// https://docs.spring.io/spring/docs/5.0.0.M5/spring-framework-reference/html/jdbc.html#jdbc-auto-genereted-keys
	@Test
	public void testInsertAutoGeneratedKey(){
		final String insertSql = "insert into t_wms_goods_stock (warehouse_id, amount) values(?,?)";
		
		KeyHolder keyHolder = new GeneratedKeyHolder();
		int insertRow = jdbcTemplate.update(new PreparedStatementCreator() {
			
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
				ps.setLong(1, 1L);
				ps.setDouble(2, 100.25);
				return ps;
			}
		}, keyHolder);
		
		if(insertRow > 0){
			System.out.println("auto-generated key:" + keyHolder.getKey());
		}
	}
	
	@Test
	public void testMultiInsertAutoGeneratedKey(){
		final String insertSql = "insert into t_wms_goods_stock (warehouse_id, amount) values(?,?),(?,?)";
		
		KeyHolder keyHolder = new GeneratedKeyHolder();
		int insertRow = jdbcTemplate.update(new PreparedStatementCreator() {
			
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
				ps.setLong(1, 1L);
				ps.setDouble(2, 100.25);
				ps.setLong(3, 2L);
				ps.setDouble(4, 122.9);
				return ps;
			}
		}, keyHolder);
		
		if(insertRow > 0){
			System.out.println("auto-generated key:" + keyHolder.getKeyList());
		}
	}
	
	@Test
	public void testStatementCallback(){
		final String sql = "select count(*) from t_wms_goods_stock";
		class ExecuteStatementCallback implements StatementCallback<Integer>, SqlProvider {

			public String getSql() {
				return sql;
			}

			public Integer doInStatement(Statement stmt) throws SQLException, DataAccessException {
				ResultSet resultSet = stmt.executeQuery(sql);
				while(resultSet.next()){
					return resultSet.getInt(1);
				}
				return null;
			}
			
		}
		
		int count = jdbcTemplate.execute(new ExecuteStatementCallback());
		System.out.println("count:" + count);
	}
	
	@Test
	public void testQuery(){
		PreparedStatementCreator psc = new PreparedStatementCreator() {
			
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				return con.prepareStatement("select count(*) from t_wms_goods_stock");
			}
		};
		
		PreparedStatementSetter pss = new PreparedStatementSetter() {
			
			public void setValues(PreparedStatement ps) throws SQLException {
				
			}
		};
		
		int count = jdbcTemplate.query(psc, null, new ResultSetExtractor<Integer>() {

			public Integer extractData(ResultSet rs) throws SQLException, DataAccessException {
				while(rs.next()){
					return rs.getInt(1);
				}
				return null;
			}
		});
		System.out.println("count:" + count);
	}
	
}
